package com.briup;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.junit.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;

public class t_customer {
    // 数据库连接四要素
    String driver = "com.mysql.cj.jdbc.Driver";
    String url = "jdbc:mysql://127.0.0.1:3306/briup";
    String username = "root";
    String password = "3365520";

    // 创建表t_customer
    @Test
    public void createTable() {
        Connection conn = null;
        Statement stmt = null;
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, username, password);
            stmt = conn.createStatement();
            String sql = "CREATE TABLE IF NOT EXISTS t_customer (" +
                    "id INT AUTO_INCREMENT PRIMARY KEY," +
                    "name VARCHAR(50) NOT NULL," +
                    "age INT," +
                    "birthday DATE," +
                    "register_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP)";
            stmt.executeUpdate(sql);
            System.out.println("t_customer表创建成功。");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
    }

    // 往表中插入多条数据
    @Test
    public void insertData() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, username, password);
            String sql = "INSERT INTO t_customer (name, age, birthday) VALUES (?, ?, ?)";
            pstmt = conn.prepareStatement(sql);
            // 插入数据示例
            pstmt.setString(1, "小丽");
            pstmt.setInt(2, 30);
            pstmt.setDate(3, new java.sql.Date(new SimpleDateFormat("yyyy-MM-dd").parse("1990-05-24").getTime()));
            pstmt.executeUpdate();
            pstmt.setString(1, "张三");
            pstmt.setInt(2, 40);
            pstmt.setDate(3, new java.sql.Date(new SimpleDateFormat("yyyy-MM-dd").parse("1980-03-15").getTime()));
            pstmt.executeUpdate();
            pstmt.setString(1, "小华");
            pstmt.setInt(2, 40);
            pstmt.setDate(3, new java.sql.Date(new SimpleDateFormat("yyyy-MM-dd").parse("2006-03-20").getTime()));
            pstmt.executeUpdate();
            pstmt.setString(1, "李四");
            pstmt.setInt(2, 40);
            pstmt.setDate(3, new java.sql.Date(new SimpleDateFormat("yyyy-MM-dd").parse("2001-09-15").getTime()));
            pstmt.executeUpdate();

            System.out.println("数据插入成功.");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null) pstmt.close();
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
    }

    // queryCustomersBornBefore2000 方法
    @Test
// 查询在2000年1月1日之前出生的客户。
    public void queryCustomersBornBefore2000() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        List<Customer> customers = new ArrayList<>();
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, username, password);
            String sql = "SELECT * FROM t_customer WHERE birthday < ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setDate(1, new java.sql.Date(new SimpleDateFormat("yyyy-MM-dd").parse("2000-01-01").getTime()));

            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Customer customer = new Customer(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getInt("age"),
                        rs.getDate("birthday"),
                        rs.getTimestamp("register_time")
                );
                customers.add(customer);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstmt != null) pstmt.close();
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }

        // 打印 Customer 对象列表
        for (Customer customer : customers) {
            System.out.println("[customer: " + customer.getName() + ", Born: " + customer.getBirthday()+"]");
        }
    }
}